package starkuz.lg.dao.impl;

import starkuz.lg.dao.ifaces.UserDao;
import starkuz.lg.model.User;
import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Repository
public class UserDaoImpl implements UserDao {

    private static final String SELECT_ALL_USERS = "select * from users";
    private static final String SELECT_USER_BY_LOGIN_AND_PASSWORD = "select * from users where login = ? and password = ?";
    private static final String SELECT_USER_BY_LOGIN = "select * from users where login = ?";
    private static final String SELECT_USERS_BY_ID = "select * from users where id = ?";
    private static final String SELECT_USERS_BY_ORDER_SITE_ID =
            "select * from users join order_sites on users.id = order_sites.ownerId where order_sites.id = ?; ";
    private static final String SELECT_USERS_BY_CTR_SITE_ID =
            "select * from users join ctr_sites on users.id = ctr_sites.ownerId where ctr_sites.id = ?; ";
    private static final String DELETE_USERS_BY_ID = "delete from users where id = ?";
    private static final String INSERT_USER =
            "insert into users (login, password, name, status, market, lastVisitDate, registrationDate) \n" +
                    "  values (?, ?, ?, ?, ? ,? ,?)";
    private static final String UPDATE_USER =
            "update users set login = ?, password = ?, name = ?," +
                    " status = ?, market = ?, lastVisitDate = ?, registrationDate = ?" +
                    " where id = ?";

    @Autowired
    private BasicDataSource ds;

    public void setDs(BasicDataSource ds) {
        this.ds = ds;
    }

    @Override
    public List<User> getAllUsers() {
        List<User> users = null;
        Connection con = null;
        Statement st = null;
        ResultSet res = null;
        try {

            con = ds.getConnection();
            st = con.createStatement();
            res =  st.executeQuery(SELECT_ALL_USERS);
            users = resultSetToUserLists(res);
            return users;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                res.close();
                st.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return new ArrayList<User>();
    }

    @Override
    public User findUserById(Integer id) {
        User user = null;
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_USERS_BY_ID);
            psql.setInt(1, id);
            user = resultSetToUser(psql.executeQuery());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return user;
    }


    @Override
    public User findUserByOrderSiteId(Integer siteId) {
        User user = null;
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_USERS_BY_ORDER_SITE_ID);
            psql.setInt(1, siteId);
            user = resultSetToUser(psql.executeQuery());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return user;
    }

    @Override
    public User findUserByCtrSiteId(Integer siteId) {
        User user = null;
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_USERS_BY_CTR_SITE_ID);
            psql.setInt(1, siteId);
            user = resultSetToUser(psql.executeQuery());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return user;
    }



    @Override
    public void addUser(User user) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con =  ds.getConnection();
            psql = con.prepareStatement(INSERT_USER);
            psql.setString(1, user.getLogin());
            psql.setString(2, user.getPassword());
            psql.setString(3, user.getName());
            psql.setString(4, User.INACTIVE);
            psql.setString(5, user.getMarket());
            psql.setDate(6, new Date(new java.util.Date().getTime())); //дата в момент создания пользователя
            psql.setDate(7, new Date(new java.util.Date().getTime()));
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void deleteUserById(Integer userId) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            psql = ds.getConnection().prepareStatement(DELETE_USERS_BY_ID);
            psql.setInt(1, userId);
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void updateUser(User user) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(UPDATE_USER);
            psql.setString(1, user.getLogin());
            psql.setString(2, user.getPassword());
            psql.setString(3, user.getName());
            psql.setString(4, user.getStatus());
            psql.setString(5, user.getMarket());
            psql.setDate(6, new Date(user.getLastVisitDate().getTime()));   //перенос даты из user
            psql.setDate(7, new Date(user.getRegistrationDate().getTime()));
            psql.setInt(8, user.getId());
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public User findUserByLoginAndPassword(String login, String password) {
        User user = null;
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_USER_BY_LOGIN_AND_PASSWORD);
            psql.setString(1, login);
            psql.setString(2, password);
            user = resultSetToUser(psql.executeQuery());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return user;


    }

    @Override
    public User findUserByLogin(String login) {
        User user = null;
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_USER_BY_LOGIN);
            psql.setString(1, login);
            user = resultSetToUser(psql.executeQuery());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return user;
    }

    private User resultSetToUser(ResultSet rs) throws SQLException {
        User user = null;
        while (rs.next()) {
            user = new User();
            user.setId(rs.getInt("id"));
            user.setName(rs.getString("name"));
            user.setLogin(rs.getString("login"));
            user.setPassword(rs.getString("password"));
            user.setStatus(rs.getString("status"));
            user.setMarket(rs.getString("market"));
            user.setLastVisitDate(rs.getDate("lastVisitDate"));
            user.setRegistrationDate(rs.getDate("registrationDate"));
        }
        return user;
    }

    private List<User> resultSetToUserLists(ResultSet rs) throws SQLException {
        List<User> users = new ArrayList<User>();
        while (rs.next()) {
            User user = new User();
            user.setId(rs.getInt("id"));
            user.setName(rs.getString("name"));
            user.setLogin(rs.getString("login"));
            user.setPassword(rs.getString("password"));
            user.setStatus(rs.getString("status"));
            user.setMarket(rs.getString("market"));
            user.setLastVisitDate(rs.getDate("lastVisitDate"));
            user.setRegistrationDate(rs.getDate("registrationDate"));
            users.add(user);
        }
        return users;
    }
}
